# !/usr/bin/env python
# -*- coding:utf-8 -*-

'''
本脚本实现以下功能：
1：采集Mysql指标信息（Mysql状态、QPS、TPS、运行时长、连接情况、缓冲池使用情况）等基本指标
2：标准化输出格式传输到本地open_agent
'''
__author__='joy'

import  os,socket
import ConfigParser
import datetime
import json
import time
#需pip安装
import pymysql.cursors,requests

mysql_info = { }

''' 1.初始化Mysql数据库 '''
def initConnect():
    #获取脚本当前路径
    root_dir = os.path.dirname(os.path.abspath('.'))
    cf = ConfigParser.ConfigParser()
    cf.read('mysql_agent.conf')

    sections = cf.sections()
    db_host = cf.get('mysql','db_host')
    db_port = cf.get('mysql', 'db_port')
    db_user = cf.get('mysql', 'db_user')
    db_pass = cf.get('mysql', 'db_pass')
    db_name = cf.get('mysql', 'db_name')
    db_charset = cf.get('mysql', 'db_charset')
    db_connect = pymysql.connect(db_host, db_user, db_pass, db_name)
    return db_connect

''' 2.获取mysql 基本信息：运行时长 '''
def getMysqlBaseInfo():
    connect = initConnect()
    param = 'Uptime'
    sql = "SHOW GLOBAL STATUS LIKE  '%s' " % (param)
    try:
        # 获取游标
        cursor = connect.cursor()
        cursor.execute(sql)
        result = cursor.fetchone()
        #运行时长 单位秒
        mysql_info['mysql.uptime'] = result[1]
    except Exception  as e:
        print "获取Mysql 基本信息出现异常，Exception info : \t", str(e)
        connect.rollback()
    finally:
        connect.close()

''' 2.获取mysql 吞吐指标 '''
def getMysqlThroughputInfo():
    connect = initConnect()
    questions_param = 'Questions'
    #累计查询次数
    questions_sql = "SHOW GLOBAL STATUS LIKE  '%s' " % (questions_param)

    com_commit_param = 'Com_commit'
    #事务提交累计次数
    com_commit_sql = "SHOW GLOBAL STATUS LIKE  '%s' " % (com_commit_param)

    com_rollback_param = 'Com_rollback'
    # 事务回滚累计次数
    com_rollback_sql = "SHOW GLOBAL STATUS LIKE  '%s' " % (com_rollback_param)

    try:
        # 获取游标
        cursor = connect.cursor()
        cursor.execute(questions_sql)
        result = cursor.fetchone()
        uptime = mysql_info.get('mysql.uptime')
        qps = int(result[1])/ float(uptime)

        mysql_info['mysql.qps'] = round(qps,2)

        cursor.execute(com_commit_sql)
        com_commit_result = cursor.fetchone()

        cursor.execute(com_rollback_sql)
        com_rollback_result = cursor.fetchone()
        tps = (int(com_commit_result[1]) + int(com_rollback_result[1]))  / float(uptime)
        mysql_info['mysql.tps'] = round(tps,2)



    except Exception  as e:
        print "获取Mysql 吞吐指标出现异常，Exception info : \t", str(e)
        connect.rollback()
    finally:
        connect.close()


''' 3.获取mysql 连接指标 '''
def getMysqlConnectInfo():
    connect = initConnect()
    max_connections_param = 'max_connections'
    #最大连接数
    max_connections_sql = "SHOW VARIABLES LIKE  '%s' " % (max_connections_param)

    threads_connected_param = 'Threads_connected'
    #当前连接数
    threads_connected_sql = "SHOW STATUS  LIKE   '%s' " % (threads_connected_param)

    connection_errors_max_connections_param = 'Connection_errors_max_connections'
    # 由 max_connections 限制导致的失败连接数
    connection_errors_max_connections_sql = "SHOW STATUS  LIKE   '%s' " % (connection_errors_max_connections_param)

    connection_errors_internal_param = 'Connection_errors_internal'
    # 服务器内部错误导致无法创建新的链接
    connection_errors_internal_sql = "SHOW STATUS  LIKE   '%s' " % (connection_errors_internal_param)
    try:
        # 获取游标
        cursor = connect.cursor()
        cursor.execute(max_connections_sql)
        result = cursor.fetchone()
        mysql_info['mysql.max_connections'] = result[1]

        cursor.execute(threads_connected_sql)
        result = cursor.fetchone()
        mysql_info['mysql.threads_connected'] = result[1]

        cursor.execute(connection_errors_max_connections_sql)
        result = cursor.fetchone()
        mysql_info['mysql.connection_errors_max_connections'] = result[1]

        cursor.execute(connection_errors_internal_sql)
        result = cursor.fetchone()
        mysql_info['mysql.connection_errors_internal'] = result[1]

    except Exception  as e:
        print "获取Mysql 连接指标出现异常，Exception info : \t", str(e)
        connect.rollback()
    finally:
        connect.close()


''' 4.获取mysql 缓冲池指标 '''
def getMysqlBufferInfo():
    connect = initConnect()
    pool_total  = 'Innodb_buffer_pool_pages_total'
    #缓冲池总页数
    pool_total_sql = "SHOW GLOBAL STATUS LIKE  '%s' " % (pool_total)

    pool_free = 'Innodb_buffer_pool_pages_free'
    #缓冲池的空闲页数
    pool_free_sql = "SHOW GLOBAL STATUS LIKE   '%s' " % (pool_free)

    pool_chunk_param = 'innodb_buffer_pool_chunk_size'
    #缓冲池块大小，默认128MB
    pool_chunk_param_sql = "SHOW GLOBAL VARIABLES  LIKE   '%s' " % (pool_chunk_param)

    pool_instances_param = 'innodb_buffer_pool_instances'
    #缓冲池实例的数量
    pool_instances_param_sql = "SHOW GLOBAL VARIABLES  LIKE   '%s' " % (pool_instances_param)
    try:
        # 获取游标
        cursor = connect.cursor()
        cursor.execute(pool_total_sql)
        pool_total_result = cursor.fetchone()

        cursor.execute(pool_free_sql)
        pool_free_result = cursor.fetchone()
        #缓冲池利用率
        mysql_info['mysql.pool_utilization'] = round((int(pool_total_result[1]) - int(pool_free_result[1])) / float(pool_total_result[1]),2)

        cursor.execute(pool_chunk_param_sql)
        pool_chunk_param_sql_result = cursor.fetchone()

        cursor.execute(pool_instances_param_sql)
        pool_instances_param_sql_result = cursor.fetchone()
        #缓冲池块数量
        mysql_info['mysql.pool_instances'] = pool_instances_param_sql_result[1]

        # 缓冲池大小（单位MB）
        mysql_info['mysql.pool_size'] = int(pool_chunk_param_sql_result[1])/1024/1024 * int(pool_instances_param_sql_result[1])
    except Exception  as e:
        print "获取Mysql 缓冲指标出现异常，Exception info : \t", str(e)
        connect.rollback()
    finally:
        connect.close()


''' 99.执行测试 '''
if __name__ == '__main__':
    getMysqlBaseInfo()
    getMysqlThroughputInfo()
    getMysqlConnectInfo()
    getMysqlBufferInfo()
    #获取当前终端名称
    this_hostname = socket.getfqdn(socket.gethostname())
    #当前时间
    ts = int(time.time())

    push_data =[]
    #迭代字典
    for key,value in mysql_info.items():
        # 定义标准传输对象
        basic_item = {
            "endpoint": this_hostname,
            "metric": "",
            "timestamp": ts,
            "step": 60,
            "value": 1,
            "counterType": "GAUGE"
        }
        basic_item['metric'] = key
        basic_item['value'] = value
        push_data.append(basic_item)
    print  push_data
    #传输到agent


    r = requests.post("http://10.138.22.15:1988/v1/push", data=json.dumps(push_data))

    print r
